# -*-encoding:utf8-*-
import xlrd,xlutils
import os
from xlutils.copy import copy#导入xlutils模块的复制excel模块

def read_cell(excel_name,sheet_index,count_dict,row_col_list):
    #打开excel
    print(excel_name)
    wb=xlrd.open_workbook(excel_name)#打开的这个excel必须存在，否则会报错
    #获取所有sheet页的名字
    #print(wb.sheet_names())
    #按名字查找第二张表单
    # sheet=wb.sheet_by_name('abc2')#根据sheet页的名字获取sheet页
    sheet = wb.sheet_by_index(sheet_index)#根据sheet页的索引获取sheet页
    #获取sheet页的行数和列数
    #打印每行信息
    #for rownum in range(sheet.nrows):  #循环取每行的数据
    #    print(sheet.row_values(rownum)[0])#取每行的数据
    #按照索引打印对应单元格内容
    for row_index,col_index in row_col_list:
        cell_A2 = sheet.cell(row_index,col_index).value#获取指定单元格的值，第一个值是列，第二个值是行
        try:
            count_dict[(row_index, col_index)] = count_dict[(row_index,col_index)] + cell_A2
        except:
            print(excel_name,row_index, col_index)

def save(excel_name,sheet_index,count_dict,row_col_list):
    rb = xlrd.open_workbook(excel_name)
    #通过sheet_by_index()获取的sheet
    #rs = rb.sheet_by_index(1)
    #复制一个excel
    wb = xlutils.copy.copy(rb)
    #通过获取到新的excel里面的sheet页
    ws = wb.get_sheet(sheet_index)
    for row_index,col_index in row_col_list:
        #cell_A2= sheet.cell(row_index,col_index).value#获取指定单元格的值，第一个值是列，第二个值是行
        #count_list[index] = count_list[index] + cell_A2
        ws.write(row_index,col_index,count_dict[(row_index,col_index)])  #写入excel，第一个值是行，第二个值是列
    wb.save('new.xls')#保存新的excel，保存excel必须使用后缀名是.xls的，不是能是.xlsx的

def control(dir_name):
    row_col_list = [(row,3) for row in range(3,168)]#169
    count_dict ={key:0 for key in row_col_list}
    sheet_index =0
    for excel_name in os.listdir(dir_name):
        if '~' not in excel_name:
            read_cell(dir_name+excel_name, sheet_index, count_dict, row_col_list)
    model_name = 'model.xlsx'
    save(model_name, sheet_index, count_dict, row_col_list)
if __name__ == '__main__':
    control(u'/home/tu/下载/2018年立法/')